package com.cola.dao;

import com.cola.utils.BasicRowProcessorFix;
import com.cola.utils.DBControl;
import com.cola.utils.DateUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * 商品管理Dao
 * @author Max
 * @since 17/09/06
 */
public class GoodsDao extends BaseDao{
    private static volatile GoodsDao instance = null;
    private static final Logger logger = LoggerFactory.getLogger(GoodsDao.class);

    public static GoodsDao getInstance() {
        if (instance == null) {
            synchronized (GoodsDao.class) {
                if (instance == null) {
                    instance = new GoodsDao();
                }
            }
        }
        return instance;
    }

    /**
     * 获取商品列表
     * @param offset 起始位置
     * @param limit 页面大小
     * @param filter
     * @return List<Map<String,Object>>
     * @author Max
     * @since 17/09/06
     */
    public List<Map<String,Object>> getGoodsList(Integer offset,Integer limit,String filter){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.*,b.`name` as goods_brand,c.`name` as goods_specifications ,d.`name` as goods_type " +
                    "FROM `cola_goods` a " +
                    "INNER JOIN system_dictionary b on a.goods_brand_no = b.d_no " +
                    "INNER JOIN system_dictionary c on a.goods_specifications_no = c.d_no " +
                    "INNER JOIN system_dictionary d on a.goods_type_no = d.d_no  "+filter+" limit ?,?";
            return getQueryRunner().query(conn,sql,new MapListHandler(new BasicRowProcessorFix()),offset,limit);
        }catch (Exception e){
            logger.error("获取商品列表异常!",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 获取商品数量
     * @param filter 搜索条件
     * @return Integer
     * @author Max
     * @since 17/09/06
     */
    public Integer getGoodsCount(String filter){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select count(goods_id) from cola_goods where status >-2 "+filter;
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                return rs.getInt(1);
            }
        }catch (Exception e){
            logger.error("获取商品数量异常!",e);
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return null;
    }

    /**
     * 添加商品
     * @param goodsList 商品信息列表
     * @return true 成功 false 失败
     * @author Max
     * @since 17/09/06
     */
    public boolean addGoods(List<Map<String,Object>> goodsList){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "insert into cola_goods(goods_name,goods_no,goods_price,sale_city,sale_area,goods_img,goods_imgs,goods_unit," +
                    "goods_brand,goods_type,goods_specifications,`desc`,`check_status`,create_time) " +
                    " values(?,?,?,?,?,?,?,?,?,?,?,?,1,?) ";
            Object[][] params = new Object[goodsList.size()][13];
            for (int i =0;i<goodsList.size();i++){
                params[i][0] = goodsList.get(i).get("goods_name");
                params[i][1] = goodsList.get(i).get("goods_no");
                params[i][2] = goodsList.get(i).get("goods_price");
                params[i][3] = goodsList.get(i).get("sale_city");
                params[i][4] = goodsList.get(i).get("sale_area");
                params[i][5] = goodsList.get(i).get("goods_img");
                params[i][6] = goodsList.get(i).get("goods_imgs");
                params[i][7] = goodsList.get(i).get("goods_unit");
                params[i][8] = goodsList.get(i).get("goods_brand");
                params[i][9] = goodsList.get(i).get("goods_type");
                params[i][10] = goodsList.get(i).get("goods_specifications");
                params[i][11] = goodsList.get(i).get("desc");
                params[i][12] = DateUtils.getCurrentDateTime();
            }
            return getQueryRunner().batch(conn,sql,params).length>0;
        }catch (Exception e){
            logger.error("添加商品信息异常",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 更新商品checkStatus
     * @param goodsId 商品id
     * @param checkStatus 状态
     * @return true 成功 false 失败
     * @author Max
     * @since 17/09/06
     */
    public boolean updateGoodsCheckStatus(Integer goodsId,Integer checkStatus){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "update cola_goods set check_status =? where goods_id =?";
            return getQueryRunner().update(conn,sql,checkStatus,goodsId)>0;
        }catch (Exception e){
            logger.error("修改商品CheckStatus异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 更新商品status
     * @param goodsId 商品id
     * @param status 状态
     * @return true 成功 false 失败
     * @author Max
     * @since 17/09/06
     */
    public boolean updateGoodsStatus(Integer goodsId,Integer status){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "update cola_goods set status =? where goods_id =?";
            return getQueryRunner().update(conn,sql,status,goodsId)>0;
        }catch (Exception e){
            logger.error("修改商品status异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 获取商品促销信息
     * @param goodsId 商品id
     * @return Map<String,Object>促销信息
     * @author Max
     * @since 17/09/06
     */
    public Map<String,Object> getGoodsSaleInfo(Integer goodsId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.*,b.giveaway_name,b.giveaway_unit,b.giveaway_specifications FROM `cola_goods` a INNER JOIN cola_giveaway b" +
                    " on a.giveaway_id = b.giveaway_id where a.goods_id =?";
            return getQueryRunner().query(conn,sql,new MapHandler(),goodsId);
        }catch (Exception e){
            logger.error("获取商品促销信息异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 根据goodsId查询商品信息
     * @param  Integer goodsId
     * @return Map<String,Object>
     * @author Max
     * @since 17/09/06
     */
    public Map<String,Object> getGoodsInfoByGoodsId(Integer goodsId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_goods where goods_id =?";
            return getQueryRunner().query(conn,sql,new MapHandler(),goodsId);
        }catch (Exception e){
            logger.error("根据goodsId查询商品信息异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 更新商品信息
     * @param goodsInfoMap 商品信息
     * @param goodsId 商品id
     * @return true 成功 false 失败
     * @author Max
     * @since 17/09/06
     */
    public boolean updateGoodsInfoByGoodsId(Map<String,Object> goodsInfoMap,Integer goodsId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "update cola_goods set check_status=(case check_status when '0' then '1' else check_status end ) " +
                    ", update_time=now() "+getUpdateGoodsInfoSql(goodsInfoMap)+" where goods_id =? ";
            return getQueryRunner().update(conn,sql,goodsId)>0;
        }catch (Exception e){
            logger.error("更新商品信息异常!",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 获取更新商品信息sql
     * @param goodsInfoMap
     * @return String
     * @author Max
     * @since 17/09/06
     */
    public String getUpdateGoodsInfoSql(Map<String,Object> goodsInfoMap){
        StringBuilder sql = new StringBuilder("");
        if(goodsInfoMap.containsKey("goods_name")){
            sql.append(" ,goods_name = '").append(goodsInfoMap.get("goods_name")).append("'");
        }

        if(goodsInfoMap.containsKey("goods_img")){
            sql.append(" ,goods_img = '").append(goodsInfoMap.get("goods_img")).append("'");
        }

        if(goodsInfoMap.containsKey("goods_imgs")){
            sql.append(" ,goods_imgs = '").append(goodsInfoMap.get("goods_imgs")).append("'");
        }

        if(goodsInfoMap.containsKey("goods_brand_no")){
            sql.append(" ,goods_brand_no = '").append(goodsInfoMap.get("goods_brand_no")).append("'");
        }

        if(goodsInfoMap.containsKey("goods_type_no")){
            sql.append(" ,goods_type_no = '").append(goodsInfoMap.get("goods_type_no")).append("'");
        }

        if(goodsInfoMap.containsKey("goods_specifications_no")){
            sql.append(" ,goods_specifications_no = '").append(goodsInfoMap.get("goods_specifications_no")).append("'");
        }

        if(goodsInfoMap.containsKey("desc")){
            sql.append(" ,`desc` = '").append(goodsInfoMap.get("desc")).append("'");
        }

        return sql.toString();
    }

    /**
     * 获取全部商品的物料编号列表
     * @return Map<String,Object>
     * @author Max
     * @since 17/09/06
     */
    public Map<String,Object> getGoodsNoMap(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            Map<String,Object> map = new HashMap<String,Object>();
            conn = DBControl.getConnection();
            String sql = "select goods_no from cola_goods ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                map.put(rs.getString(1),"");
            }
            return map;
        }catch (Exception e){
            logger.error("获取全部商品的物料编号列表异常",e);
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return null;
    }

    /**
     * 插入新物料信息
     * @param conn 链接
     * @param goods_no 物料编号
     * @param goods_name 物料名称
     * @param goods_brand_no 品牌编号
     * @param goods_type_no 类型编号
     * @param goods_specifications_no 规格编号
     * @param desc 描述
     * @return true 成功；false 失败
     * @author Max
     * @since 2017/9/7
     */
    public boolean addOrUpdateGoodsInfo(Connection conn,boolean alreadyHas,String goods_no,String goods_name
            , String goods_brand_no,String goods_type_no,String goods_specifications_no,String desc) throws Exception {
        String sql ;
        try{
            if(alreadyHas){
                sql = "update cola_goods set goods_name=?,goods_brand_no=?,goods_type_no=?,goods_specifications_no=?,`desc`=? where goods_no=?";
                return getQueryRunner().update(conn,sql,goods_name,goods_brand_no,goods_type_no,goods_specifications_no,desc,goods_no)>0;
            }else{
                sql = "insert into cola_goods(goods_no,goods_name,goods_unit_no,goods_brand_no,goods_type_no,goods_specifications_no,`desc`,check_status,create_time)" +
                        " values(?,?,1,?,?,?,?,0,now())";
                return getQueryRunner().update(conn,sql,goods_no,goods_name,goods_brand_no,goods_type_no,goods_specifications_no,desc)>0;
            }
        }catch (Exception e){
            logger.error("更新或插入物料信息异常！",e);
            throw e;
        }
    }



    //================================赠品======================================

    /**
     * 获取赠品列表
     * @param offset 起始位置
     * @param limit 页面大小
     * @param filter 搜索条件
     * @return List<Map<String,Object>>
     * @author Max
     * @since 17/09/06
     */
    public List<Map<String,Object>> getGiveawayList(Integer offset,Integer limit,String filter){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.* ,b.`name` as giveaway_brand,c.`name` as giveaway_specifications ,d.`name` as giveaway_type" +
                    ", f.`name` as giveaway_unit  FROM `cola_giveaway` a " +
                    "INNER JOIN system_dictionary b on a.giveaway_brand_no = b.d_no " +
                    "INNER JOIN system_dictionary c on a.giveaway_specifications_no = c.d_no " +
                    "INNER JOIN system_dictionary d on a.giveaway_type_no = d.d_no " +
                    "INNER JOIN system_dictionary f on a.giveaway_unit_no = f.d_no  where a.status >-2 "+filter+" limit ?,?";
            return getQueryRunner().query(conn,sql,new MapListHandler(new BasicRowProcessorFix()),offset,limit);
        }catch (Exception e){
            logger.error("获取赠品列表!",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 获取赠品列表
     * @return List<Map<String,Object>>
     * @author Max
     * @since 17/09/06
     */
    public List<Map<String,Object>> getAllGiveawayList(){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_giveaway where status >-2 ";
            return getQueryRunner().query(conn,sql,new MapListHandler(new BasicRowProcessorFix()));
        }catch (Exception e){
            logger.error("获取赠品列表!",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 获取赠品数量
     * @param String filter
     * @return Integer
     * @author Max
     * @since 17/09/06
     */
    public Integer getGiveawayCount(String filter){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select count(a.giveaway_id)FROM `cola_giveaway` a " +
                    "INNER JOIN system_dictionary b on a.giveaway_brand_no = b.d_no " +
                    "INNER JOIN system_dictionary c on a.giveaway_specifications_no = c.d_no " +
                    "INNER JOIN system_dictionary d on a.giveaway_type_no = d.d_no " +
                    "INNER JOIN system_dictionary f on a.giveaway_unit_no = f.d_no  where a.status >-2 "+filter;
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                return rs.getInt(1);
            }
        }catch (Exception e){
            logger.error("获取赠品数量异常!",e);
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return null;
    }

    /**
     * 添加赠品
     * @param giveawayMap
     * @return true 成功 false 失败
     * @author Max
     * @since 17/09/06
     */
    public boolean addGiveaway(Map<String,Object> giveawayMap){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "insert into cola_giveaway(giveaway_no,giveaway_name,giveaway_img,giveaway_imgs,giveaway_unit_no,giveaway_brand_no,giveaway_type_no," +
                    "giveaway_specifications_no,`desc`,create_time ) " +
                    " values(?,?,?,?,?,?,?,?,?,?) ";
            return getQueryRunner().update(conn,sql,giveawayMap.get("giveaway_no"),giveawayMap.get("giveaway_name"),giveawayMap.get("giveaway_img"),giveawayMap.get("giveaway_imgs"),giveawayMap.get("giveaway_unit_no")
                    ,giveawayMap.get("giveaway_brand_no"),giveawayMap.get("giveaway_type_no"),giveawayMap.get("giveaway_specifications_no")
                    ,giveawayMap.get("desc"), DateUtils.getCurrentDateTime())>0;
        }catch (Exception e){
            logger.error("添加赠品信息异常",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 更新赠品status
     * @param giveawayId
     * @return true 成功 false 失败
     * @author Max
     * @since 17/09/06
     */
    public boolean updateGiveawayStatus(Integer giveawayId,Integer status){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "update cola_giveaway set status =? where giveaway_id =?";
            return getQueryRunner().update(conn,sql,status,giveawayId)>0;
        }catch (Exception e){
            logger.error("更新赠品status异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }




}
